using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
//using System.Web.Configuration;
using System.Configuration;
using System.Diagnostics;
using bldrdb;
using bldrdbimpl;


/// <summary>
/// Summary description for dblayer2
/// </summary>
/// 



namespace bldrdb
{


    
public enum DBPlatformType { mssql, oracle, dbtNdef};


//public delegate void DOnLog(String AMessage);


public struct DBOptions
{

    public int DefaultTimeOut;
};

   

public interface IStorProc: IDisposable
{
    // Time in seconds
    void SetTimeout(int ATimeout);
    int GetTimeout();

    void SetSQL(string sql);
    void SetProcName(String AProcName);

    void StartTransaction();
    void CommitTransaction();
    void RollbackTransaction();

    void AddParam(String AParamName, String AParamValue);
    void AddOutStringParam(String AParamName);

    //Need Transaction to call this method
    void AddBlobParam(string AParamName, byte[] ABuffer, int count);

//    String GetParamValue(String AParamName);

    //use this method only if stored procedure return resultset
    IResultSet OpenResultSet();

    //use this method only if stored procedure do not return resultset
    void Execute();

    IStorProc Spawn();

};

public interface IResultSet
{
    bool Read();
    string GetField(string AFieldName);
    bool GetBoolean(string AFieldName);
    byte[] GetBlob(string AFieldName);
    DateTime GetDateTime(string AFieldName);
    bool GetIsNull(string AFieldName);
    int GetInt(string AFieldName);
    Int64 GetInt64(string AFieldName);
    //System.IO.Stream GetBlobStream(string AFieldName);
    void Close();
    string this[string index] { get;}
};





    public static class MainDB
    {
        static DBFactory factory=   null;

        public static string __constring;
        public static DBFactory instance()
        {
            if (factory == null)
            {
                DBFactory newfactory = new DBFactory();

              
                    //connString=System.Web.Configuration.WebConfigurationManager.ConnectionStrings["local"].ConnectionString;
                  //  connString = System.Configuration.ConfigurationManager.ConnectionStrings["local"].ConnectionString;

                    newfactory.SetOptions(__constring);
              


                factory=newfactory;
            }

            return factory;
        }

    }

    public class DBFactory
    {
        public string FConnString, FProviderName;

        public DBFactory()
        {
            //System.Data.SqlClient;
            //System.Data.OracleClient

        }

        public void SetOptions(string AConnectionString)
        {
            FConnString = AConnectionString;

        }

        public IStorProc CreateStorProc()
        {
            IStorProc stor_proc = null;


            stor_proc = new MSStorProc(FConnString);

            return stor_proc;
        }

        public static IStorProc Create()
        {
            IStorProc stor_proc = null;

            //    ConnectionStringSettings cssc = ConfigurationManager.ConnectionStrings[name];



            string connString = MainDB.instance().FConnString; ;


            stor_proc = new MSStorProc(connString);

            return stor_proc;
        }

        public static String ConvertSearchWildCard(String aValue)
        {


            bool ismssql = true;

            String ret = "";
            for (int i = 0; i < aValue.Length; i++)
            {
                String dstsmb;
                char ch = aValue[i];
                switch (ch)
                {
                    case '*':
                        dstsmb = "%";
                        break;
                    case '?':
                        dstsmb = "_";
                        break;
                    case '%':
                    case '_':
                    case '\\':
                        dstsmb = "\\" + ch;
                        break;
                    case '[':
                        if (ismssql) //for ms sql server only
                            dstsmb = "\\" + ch;
                        else
                            dstsmb = ch.ToString();
                        break;
                    default:
                        if (ismssql && ch == '^' && i > 0 && aValue[i - 1] == '[') //for ms sql server only
                            dstsmb = "\\[\\^";
                        else
                            dstsmb = ch.ToString();
                        break;
                }
                ret += dstsmb;
            }
            return ret;
        }

    };

}


// Don't use classes from NV.DB namespace directly.
// Those classes are implementation interfaces from NV namespase.
namespace bldrdbimpl
{



abstract public  class StorProc: IStorProc
{

    //static Log logger = new Log("StorProc");

    protected IDbCommand FCommand;
    protected IDataReader  FReader;

    protected DateTime debug_dt_create;
    //protected DateTime debug_dt_create;

    //protected IDbTransaction FTransaction;

    public abstract IStorProc Spawn();
    /*
    static StorProc Create(string connectionString)
    {
        StorProc res = null;
        return res;
    }
     * */

    

    public static void EnumSqlParamsInExpression(string sql, List<string> sqlParams)
    {
        

        


        int dog_pos = sql.IndexOf("@");
        while (dog_pos > 0)
        {
            sql = sql.Substring(dog_pos+1,sql.Length-(dog_pos+1));

            char[] delims ={ '@', ',', ' ', '\r', '\n' };
            string[] fragments = sql.Split(delims, StringSplitOptions.RemoveEmptyEntries);
            sqlParams.Add(fragments[0] );


            dog_pos = sql.IndexOf("@");
        }

    }


    public IDbCommand get_Command()
    {
        return FCommand;
    }

    
    public void SetSQL(string sql)
    {
        closeFReader();
        FCommand.Parameters.Clear();

        string s = sql;

        FCommand.CommandText = s;
        FCommand.CommandType = CommandType.Text;
        
    }
    public void prepareParams()
    {
        List<string> sqlParams=new List<string>();
        EnumSqlParamsInExpression(FCommand.CommandText, sqlParams);
       // FCommand.Prepare();
    }
    protected void closeFReader()
    {
        if (FReader != null)
        {
            if (!FReader.IsClosed)
            {
                //System.Diagnostics.Trace.WriteLine("!FReader.IsClosed");
                FReader.Close();
            }
        }
    }
    public void SetSQLBlock(string sql)
    {
        closeFReader();
        FCommand.Parameters.Clear();

        string s = sql;
        s = "begin \r\n" + s + "\r\nend;";

        FCommand.CommandType = CommandType.Text;
        
    }

    public void Dispose()
    {

        long lifetime = (long)(DateTime.Now - debug_dt_create).TotalMilliseconds;
        
        string mes = FCommand.CommandText;

        if (mes.Length > 80)
            mes = mes.Substring(0, 79);
        mes.Replace('\n', ' ');
        mes.Replace('\r', ' ');

        mes += "\r\n      ---- " + lifetime.ToString() + "ms";
        //logger.write(mes);

        FCommand.Connection.Close();
    }

    public void StartTransaction()
    {
        //FTransaction = FCommand.Connection.BeginTransaction();
        //FCommand.Transaction = FTransaction;
        FCommand.Transaction = FCommand.Connection.BeginTransaction();
    }

    public void CommitTransaction()
    {
       // FTransaction.Commit();
        FCommand.Transaction.Commit(); 
    }

    public void RollbackTransaction()
    {
        //FTransaction.Rollback();

        if (FCommand.Transaction == null)
            throw new Exception("FCommand.Transaction == null");

        FCommand.Transaction.Rollback();
    }
   
    public void SetTimeout(int ATimeout)
    {
        //Unlike the Command object in the other .NET Framework data providers (SQL Server, OLE DB, and ODBC), 
        //the OracleCommand object does not support a CommandTimeout property. 
        //Setting a command timeout has no effect and the value returned is always zero.
        //http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.aspx
        FCommand.CommandTimeout = ATimeout;
    }

    public int GetTimeout()
    {
        return FCommand.CommandTimeout;
    }

    public void SetProcName(String AProcName)
    {
        closeFReader();
        FCommand.Parameters.Clear();

        FCommand.CommandType = CommandType.StoredProcedure;
        FCommand.CommandText = AProcName;
    }

    protected void AddParamInt(String AParamName, String AParamValue)
    {
        IDbDataParameter param = FCommand.CreateParameter();

        param.Direction = ParameterDirection.Input;
        param.ParameterName = AParamName;
        param.Value = AParamValue;
       // param.DbType = DbType.Int32;

        FCommand.Parameters.Add(param);
    }

    public void AddOutStringParamInt(String AParamName)
    {
        IDbDataParameter param = FCommand.CreateParameter();

        param.Direction = ParameterDirection.Output;
        param.ParameterName = AParamName;
        param.DbType = DbType.String;
        param.Size = 255;

        FCommand.Parameters.Add(param);
    }


    public String GetParamValueInt(String AParamName)
    {
        int i = FCommand.Parameters.IndexOf(AParamName);
        if (i == 0) throw new Exception("Parameter is not exist.");
        
        IDbDataParameter param = (IDbDataParameter)FCommand.Parameters[i];
        return param.Value.ToString();
    }

    //use this method only if stored procedure does not return resultset
    [DebuggerNonUserCode]
    public virtual void Execute()
    {
        prepareParams();
        try
        {
            FCommand.ExecuteNonQuery();
        }
        catch (Exception E)
        {
            throw new Exception(E.Message + "Proc.Execute(" + FCommand.CommandText + ") ", E);
        }
    }
    protected string FFullConnectionString;
    //public abstract void SetProcName(String AProcName);
    public abstract IResultSet OpenResultSet();
    public abstract void AddParam(String AParamName, String AParamValue);
    public abstract void AddOutStringParam(String AParamName);
    public abstract string GetParamValue(String AParamName);

    public abstract void AddBlobParam(string AParamName, byte[] ABuffer,int count);
    //public abstract System.IO.Stream AddBlobParamStream(string AParamName);
};

public sealed class MSStorProc : StorProc, IStorProc
{
    
    public MSStorProc(string AConnectionString)
    {
        debug_dt_create = DateTime.Now;

        FFullConnectionString = AConnectionString;
        FCommand = new SqlCommand();
        FCommand.Connection = new SqlConnection();
        FCommand.Connection.ConnectionString = AConnectionString;
        FCommand.Connection.Open();
    }


    public override IStorProc Spawn()
    {

        StorProc nsp = new MSStorProc(FFullConnectionString);
        return nsp;
    }
    /*
    public override System.IO.Stream AddBlobParamStream(string AParamName)
    {

        return null;
    }
     */
    public override void AddParam(String AParamName, String AParamValue)
    {
        AddParamInt(AParamName, AParamValue);
    }


    public override void AddOutStringParam(String AParamName)
    {
        AddOutStringParamInt(AParamName);
    }

     public override void AddBlobParam(string AParamName, byte[] ABuffer,int count)
    {
        IDbDataParameter param = FCommand.CreateParameter();

        param.Direction = ParameterDirection.Input;
        param.ParameterName = AParamName;
        param.DbType = DbType.Binary;
        param.Size = count;
        param.Value = ABuffer;

        FCommand.Parameters.Add(param);
    }

    
    public override string GetParamValue(String AParamName)
    {
        return GetParamValueInt(AParamName);
    }

    [DebuggerNonUserCode]
    public override IResultSet OpenResultSet()
    {
        prepareParams();
        FReader = FCommand.ExecuteReader();
        ResultSet Result = new ResultSet(FReader, DBPlatformType.mssql);
        //System.Diagnostics.Trace.WriteLine("ExecuteReader() " + FCommand.CommandText);
        return Result;   
    }
};



public sealed class ResultSet: IResultSet
{
    IDataReader FReader;
    public DBPlatformType FType;

    public IDataReader get_IDataReader()
    {
        return FReader;
    }

    public ResultSet(IDataReader AReader, DBPlatformType AType)
    {
        FReader = AReader;
        FType = AType;
    }

    public void Close()
    {
        //System.Diagnostics.Trace.WriteLine("  FReader.Close()");
        FReader.Close();
    }

    public bool Read()
    {
        return FReader.Read();
    }

    public string this[string index]
    {
        get
        {
            return GetField(index);
        }

    }
    public string GetField(string AFieldName)
    {
        string value = "";
        
        int index = FReader.GetOrdinal(AFieldName);
        string type = FReader.GetDataTypeName(index);

        if (type == "bit")
        {
            bool bval =  GetBoolean(AFieldName);

            if (bval)
                value = "1";
            else
                value = "0";
        }
        else
        {
            value = FReader.GetValue(index).ToString();
        }

        return value;
    }
    
    public DateTime GetDateTime(string AFieldName)
    {
        int index = FReader.GetOrdinal(AFieldName);
        DateTime ret = FReader.GetDateTime(index);
        
        return ret;
    }
    public int GetInt(string AFieldName)
    {
        int index = FReader.GetOrdinal(AFieldName);
        int ret = FReader.GetInt32(index);
        
        return ret;
    }
    public Int64 GetInt64(string AFieldName)
    {
        int index = FReader.GetOrdinal(AFieldName);
        Int64 ret = FReader.GetInt64(index);

        return ret;
    }
    
    public bool GetBoolean(string AFieldName)
    {
        //bool value;
        int index = FReader.GetOrdinal(AFieldName);
        if (FReader.IsDBNull(index))
        {
            return false;
        }
        return intGetBoolean(index);
    }
    public bool intGetBoolean(int index)
    {
        bool value;
        if (FType == DBPlatformType.mssql)
        {    
            value = FReader.GetBoolean(index);
        }
        else
        {
            value = true;
            
            int ival = FReader.GetInt32(index);
            if (ival == 0) value = false;
        }

        return value;
    }

    public bool GetIsNull(string AFieldName)
    {
        int index = FReader.GetOrdinal(AFieldName);
        bool ret = FReader.IsDBNull(index);
        
        return ret;
    }
    

   


    public byte[] GetBlob(string AFieldName)
    {

        int index = FReader.GetOrdinal(AFieldName);
        if (FReader.IsDBNull(index))
        {
            return new byte[0];
        }

        byte[] res_buff = (byte[])FReader[index];
        return res_buff;
    }
};

    

}



